March 28, 2024, 12:47:05 PM

News:

Own IWBasic 2.x ? -----> Get your free upgrade to 3.x now.........


Plus 255 characters SQL error

Started by Andy, December 09, 2018, 04:19:59 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Andy

December 09, 2018, 04:19:59 AM Last Edit: December 09, 2018, 04:25:58 AM by Andy
Hi,

I have ISTRINGS to store variables such a lName (Last name), and it is set to "Memo" type in the DB table.

But when I try to increase this size to say 300 from 255 I get SQL error 07002.

All corresponding fields (as far as I can see) are set to 300 - what am I doing wrong here anyone?

iStmt="UPDATE customers SET lName=?,fName=?,oName=?,sex=?,street=?,city=?,region=?,"
iStmt+="tk=?,phone=?,mobile=?,facebook=?,dob=?,age=?,email=?,cwork=?,disc=?,notes=?,cphoto=?"
iStmt+=" WHERE recID="+STR$(id)
hStmt=dbPrepareSQL(pDB,iStmt)
dbBindParameter(hStmt,1,lName,300)  '<------------------- Here!
dbBindParameter(hStmt,2,fName,200)
dbBindParameter(hStmt,3,oName,200)
dbBindParameter(hStmt,4,sex,5)
dbBindParameter(hStmt,5,street,200)
dbBindParameter(hStmt,6,city,40)
dbBindParameter(hStmt,7,region,40)
dbBindParameter(hStmt,8,tk,10)
dbBindParameter(hStmt,9,phone,40)
dbBindParameter(hStmt,10,mobile,40)
dbBindParameter(hStmt,11,facebook,40)
dbBindParameter(hStmt,12,isoDate,11)
dbBindParameter(hStmt,13,age,5)
dbBindParameter(hStmt,14,email,40)
dbBindParameter(hStmt,15,cwork,40)
dbBindParameter(hStmt,16,disc,8)
dbBindParameter(hStmt,17,notes,255)
dbBindParameter(hStmt,18,cphoto,255)
   endif

dbExecute(hStmt)
IF LEN(dbGetErrorCode(hStmt))
             
' <--------- Error message here
MESSAGEBOX w1,"("+STR$(__LINE__)+") "+dbGetErrorText(hStmt)+"\n"+iStmt,dbGetErrorCode(hStmt)

ENDIF
dbFreeSQL(hStmt)
hStmt=NULL


Please remember I know very little about databases!

Thanks,
Andy.
Day after day, day after day, we struck nor breath nor motion, as idle as a painted ship upon a painted ocean.

Brian

Andy,

I don't see MEMO being referenced in IWB. Why not ISTRING your variable to the required length - lName ISTRING(300), and then when you call CREATE TABLE, set the column name lName as lName VARCHAR(300)? That should work

Brian

billhsln

Tried VARCHAR(300), gives error.  I am looking into setting the 'Unicode Compression' to YES, which might mean you could leave the field as 255 and let Access handle the Unicode for you.  However, so far, I have not found a way to turn Unicode Compression on from within the program.  If you have ACCESS you can change after the DB is created, but that is not the solution that will solve every one else who tries this.

Also note that MEMO is now LONGTEXT when using "Microsoft Access Driver (*.mdb, *.accdb)" driver, if you have it.

Bill

Test program attached, use CONSOLE to compile.
When all else fails, get a bigger hammer.

billhsln

Found a way around and it does save into the Memo field.

See attached program.  However, still going to look into using Unicode Compression.

Bill
When all else fails, get a bigger hammer.

LarryMc

How I use memo fields in programs like my Snippet Manager

1) When I create the field in the table I define it as "snip longtext"
2)When doing it this way the size of what I can store is determined solely by the variable I use; in my case  --   istring snip[100001]
3) and the proper syntax for binding would be -- dbBindParameter(hstmt,8,snip,255)  <- notice the 255

I've never tried Brian's way for strings over 255 but I've use it many times for very short strings and it works very well.
If it indeed works the same over 255 as it does for under 255 then his method would save memory over using a memo field since memo fields consume memory in blocks.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

billhsln

December 09, 2018, 01:28:57 PM #5 Last Edit: December 09, 2018, 01:32:52 PM by billhsln
Larry, I have tried to do 'WITH COMP' and 'WITH COMPRESSION' to turn on the Unicode Compression on a TEXT/LONGTEXT field and it gives me an error.  Just wondering if you might know a way to turn the COMPRESSION on?

Thanks,
Bill

PS, found this:

Note The WITH COMPRESSION and WITH COMP keywords listed in the previous SQL statements can be executed only through the Jet OLE DB provider and ADO. They will result in an error message if used through the Access SQL View user interface.

Never used Jet OLE DB provider or ADO, so will need to see if we can do this from IWB.
When all else fails, get a bigger hammer.

LarryMc

I looked through the include files for the iwdb source code and found extensive use of a compiler directive ODBCVER30 if defining consts and declaring functions.
I found a note mentioning ODBCVER35 and UNICODE but no alternate const or function definitions.

Bottom line is that I will be of no help. Sorry
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Andy

Thanks guys,

The reason I'm trying to exceed 255 characters is this:

In my database, I'm storing data as Unicode strings, here is an example:

200,3,187,3,187,3,187,3,187,3,187,3,187,3,0,0

As you can see it is using up much more space than single ANSII characters - but it's the only practical way to store Unicode that I've found.

So at the moment I can store around 42 Unicode characters as a string which fits in to 255,
Now that's okay for a first, last, other name etc, but not if you wanted a field where you can type some notes on the customer.

At the moment I'm using Bill's older suggestion and splitting up notes into two fields - notes and notes2.

I'm restricting the edit fields to 40 chars, so two note fields will give me 80 chars - still think I need a couple more.

But I think it would be interesting to see if we can exceed this in IWB.

Thanks,
Andy.

Day after day, day after day, we struck nor breath nor motion, as idle as a painted ship upon a painted ocean.

LarryMc

Andy
Did you try my way of writing a memo field and binding and it didn't work with your unicode data?

If you did try it and it worked then I believe there is a simple solution solution to to "compress" your dbase file ro regain no longer used memory.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Andy

Thanks Larry,

Yes I did try it, and no it didn't work, nor does Bills method.

Do I need to do an SQL / something update my end?  :o

Andy.


Day after day, day after day, we struck nor breath nor motion, as idle as a painted ship upon a painted ocean.

LarryMc

This is the source for the "bind" command.
SUB dbBindVariable(hstmt as INT,column as INT,variable as ANYTYPE,opt pReturn=NULL as POINTER,opt cbSize=255 as INT),INT
DEF rc as WORD
DEF typ as WORD
DEF pParam as POINTER
DEF length as INT
pParam = NULL
length = 0
indTemp = 0
rc = FALSE
IF pReturn = NULL THEN pReturn = indTemp
IF hstmt AND ISREF(variable)
SELECT TYPEOF(variable)
CASE @TYPEINT
length = 0
pParam = #<POINTER>variable
typ = SQL_C_LONG
CASE @TYPEUINT
length = 0
pParam = #<POINTER>variable
typ = SQL_C_ULONG
CASE @TYPEWORD
length = 0
pParam = #<POINTER>variable
typ = SQL_C_USHORT
CASE @TYPESWORD
length = 0
pParam = #<POINTER>variable
typ = SQL_C_SHORT
CASE @TYPECHAR
length = 1
pParam = #<POINTER>variable
typ = SQL_C_CHAR
CASE @TYPESCHAR
length = 1
pParam = #<POINTER>variable
typ = SQL_C_CHAR
CASE @TYPESTRING
length = cbSize
pParam = ##<STRING>variable
typ = SQL_C_CHAR
CASE @TYPEFLOAT
length = 0
pParam = #<POINTER>variable
typ = SQL_C_FLOAT
CASE @TYPEDOUBLE
length = 0
pParam = #<POINTER>variable
typ = SQL_C_DOUBLE
CASE @TYPEINT64
length = 0
pParam = #<POINTER>variable
typ = SQL_C_SBIGINT
CASE @TYPEUINT64
length = 0
pParam = #<POINTER>variable
typ = SQL_C_UBIGINT
default
length = cbSize
pParam = #<POINTER>variable
typ = SQL_C_BINARY
ENDSELECT
rc = SQLBindCol(hstmt,column,typ,pParam,length,pReturn)
IF(rc = SQL_SUCCESS) OR (rc =  SQL_SUCCESS_WITH_INFO) THEN rc = TRUE ELSE rc = FALSE
ENDIF
RETURN rc
ENDSUB

Notice that this "SQL_C_WCHAR" is missing.  I don't know enough to know what else is missing elsewhere to make it all work.

LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Rock Ridge Farm (Larry)

How is the field defined in your SQL DB - Varchar allows a larger field size.

Andy

December 11, 2018, 03:31:03 AM #12 Last Edit: December 11, 2018, 04:48:00 AM by Andy
Thanks every one for your help on this.

What I have found out is that the data is indeed being stored, that is longer than 255 characters in the DB.

However, it's only reading back to the maximum of a normal string, even though the field in an ISTRING.

Attached is my old Unicode DB example, thought it would be easier to play around with this one rather than my main program.

You will see that the "Product" field is defined as ISTRING Product[2000] (made it large so there's no mistakes).
In the console screen that opens, you will see me printing

print "Product Characteer 255 = ",Product[255]


This returns nothing, if you set it to say 25 you get the character.

So the problem seems to be retrieving more than 254 charaters.

Help!

Andy.

Day after day, day after day, we struck nor breath nor motion, as idle as a painted ship upon a painted ocean.

Rock Ridge Farm (Larry)

How is product defined? if product is defined as product[255] that would be 0-254 so if you access pos 255 you would be beyond the definition. Just one of those things I have often done.

Andy

Hi Larry,

The variable Product is an ISTRING with space for 2000 characters.

The dbbind is 255 as every one tells me thats okay.

As I said, it is saving beyond 255 characters, just not reading them back in.

Don't know yet if it's a DB problem or if it's the code that translates it.

Andy.
Day after day, day after day, we struck nor breath nor motion, as idle as a painted ship upon a painted ocean.

fasecero

OK, this is what I learned by looking at the web, the sample projects and the help file.

- Prior to MySQL 5.0.3, a VARCHAR could only store up to 255 characters.
- Help file mentions BINARY as a data type that DBMS should support. But I could never make it to work properly with dbBindParameter (tried unicode buff, int array, UDT).
- After many tests, I found out that VARBINARY can accept unicode data inside dbPrepareSQL if it's entered as an hexagesimal string. I've seen hex strings on some old codes before, but this is actually the first time I need to use them. For instance the hex string of L"ab" would be "0x610062000000"
- VARBINARY also seems to have its own limit of 510 bytes. which means that it can save up to 255 unicode characters at most.
- dbBindVariable binds unicode data without any issue, which is surprising.

Putting all of this together I have come up with an example that seems to work fine, it may have mistakes, I learned through this topic on how to write/read a database for the first time. If you can't break the 255 ansi character limit here's an alternative, but it requires many changes to your code.

Run once and it should create the database, 2 VARCHAR ansi fields and one VARBINARY unicode field. The exe directory needs write access permission.
Close it and run it again to read the data back in and print it to the console.


$INCLUDE "windowssdk.inc"
$INCLUDE "shlwapi.inc"

IWSTRING buffer[255] ' 510 seems to be max allowed size for BINARY

OPENCONSOLE
PRINT

IF PathFileExists(GETSTARTPATH() + "mydatabase.mdb") = 0 THEN CreateDatabase() ELSE ReadDatabase()

PRINT
PRINT "Press any key to exit"
DO:UNTIL INKEY$ <> ""
CLOSECONSOLE

SUB ReadDatabase()
'make a connection to the newly created database
pointer pdb = dbConnect("Microsoft Access Driver (*.mdb)", GETSTARTPATH() + "mydatabase.mdb", "")

IF pdb THEN
string first, last
INT branch_null

INT hStmt = dbExecSQL(pdb,"SELECT * FROM account")

IF hStmt THEN
dbBindVariable(hstmt, 1, last)
dbBindVariable(hstmt, 2, first)
dbBindVariable(hstmt, 3, buffer, branch_null, 510)

print "Reading database..."
print

WHILE dbGet(hStmt)
print "First ansi name: ", first
print "Last ansi name: ", last
print "Unicode data: " + w2s(buffer)
print
ENDWHILE

dbFreeSQL(hStmt)
ENDIF

dbDisconnect(pdb)
ENDIF
ENDSUB

SUB CreateDatabase()
'create a database
INT dbH = dbCreateMDB(GETSTARTPATH() + "mydatabase.mdb")

IF dbH THEN
'make a connection to the newly created database
pointer pdb = dbConnect("Microsoft Access Driver (*.mdb)", GETSTARTPATH() + "mydatabase.mdb", ";")

IF pdb THEN
'create a table and fields
INT hstmt = dbExecSQL(pdb, "CREATE TABLE account (lname VARCHAR(255),fname VARCHAR(255),other VARBINARY(510))" )

IF hstmt THEN
'add database record
DatabaseAddRecord(pdb, "Miller", "Lisa", L"Some unicode info about Lisa")
DatabaseAddRecord(pdb, "Miller", "Tammy", L"Some unicode info about Tammy")
dbFreeSQL(hstmt)

PRINT "Database created. Restart the console."
ENDIF

dbDisconnect(pdb)
ENDIF
ENDIF
ENDSUB

SUB DatabaseAddRecord(pointer pdb, string lastname, string firstname, wstring unicodeData)
' convert wstring unicodeData -> string unicodeHEX
string unicodeHEX = WstringToHexString(unicodeData)

INT hstmt = dbPrepareSQL(pdb,"INSERT INTO account (lname,fname,other) VALUES(?,?," + unicodeHEX + ")")

IF hstmt
string first, last
dbBindParameter(hstmt, 1, last, 255)
dbBindParameter(hstmt, 2, first, 255)
first = firstname
last = lastname
dbExecute(hstmt)

dbFreeSQL(hstmt)
ENDIF
ENDSUB

SUB WstringToHexString(wstring unicodeData), string
string unicodeHEX = "0x"
pointer p = &unicodeData + 0
char a = 0
char b = 0

DO
a = *<char>p
int t = ASC(chr$(a))
unicodeHEX += GetHEX(t)
b = *<char>(p+1)
int t2 = ASC(chr$(b))
unicodeHEX += GetHEX(t2)
p+=2
UNTIL a = 0 AND b = 0

RETURN unicodeHEX
ENDSUB

SUB GetHEX(int t), string
string hex = HEX$(t)
if hex = "0" THEN hex = "00"
RETURN hex
ENDSUB

Andy

December 12, 2018, 05:59:00 AM #16 Last Edit: December 12, 2018, 06:28:00 AM by Andy
Fasecero,

Thank you, you've (any many others) have put a lot of work into this - as I have done too.

Well I've managed to create a new DB with one of the fields called "notes" which is set to varbinary(510) - that is Binary type field.

I have a Unicode edit control called IDE_notes, and I'm trying to update this field with Unicode characters but again I have a problem.

If I type in English (in the control IDE_notes) the update to the DB works.
If I type in another language, it doesn't - just stores nonsense.

So these are the parts of the code:

wstring DisplayText  = L""

CreateWindowW(L"EDIT", L"",WS_VISIBLE|WS_CHILD|SS_LEFT|0x200, 90,310,820,20, w1.hwnd, IDE_notes, GetModuleHandle(0), 0)

Now this is what I have for the update section...

      GetWindowTextW(GetDlgItem(w1.hwnd,IDE_notes),DisplayText,255)
      string notesx = WstringToHexString(DisplayText)

iStmt="UPDATE customers SET lName=?,fName=?,oName=?,sex=?,street=?,city=?,region=?,"
iStmt+="tk=?,phone=?,mobile=?,facebook=?,dob=?,age=?,email=?,cwork=?,disc=?,notes="+notesx+",notes2=?,notes3=?,notes4=?,cphoto=?"
iStmt+=" WHERE recID="+STR$(id)
hStmt=dbPrepareSQL(pDB,iStmt)
dbBindParameter(hStmt,1,lName,200)
dbBindParameter(hStmt,2,fName,200)
dbBindParameter(hStmt,3,oName,200)
dbBindParameter(hStmt,4,sex,5)
dbBindParameter(hStmt,5,street,200)
dbBindParameter(hStmt,6,city,40)
dbBindParameter(hStmt,7,region,40)
dbBindParameter(hStmt,8,tk,10)
dbBindParameter(hStmt,9,phone,40)
dbBindParameter(hStmt,10,mobile,40)
dbBindParameter(hStmt,11,facebook,40)
dbBindParameter(hStmt,12,isoDate,11)
dbBindParameter(hStmt,13,age,5)
dbBindParameter(hStmt,14,email,40)
dbBindParameter(hStmt,15,cwork,40)
dbBindParameter(hStmt,16,disc,8)
dbBindParameter(hStmt,17,notes,0,255)
dbBindParameter(hStmt,18,notes2,255)
dbBindParameter(hStmt,19,notes3,255)
dbBindParameter(hStmt,20,notes4,255)
dbBindParameter(hStmt,21,cphoto,255)


And finally...

SUB WstringToHexString(wstring unicodeData), string
string unicodeHEX = "0x"
pointer p = &unicodeData + 0
char a = 0
char b = 0

DO
a = *<char>p
int t = ASC(chr$(a))
unicodeHEX += GetHEX(t)
b = *<char>(p+1)
int t2 = ASC(chr$(b))
unicodeHEX += GetHEX(t2)
p+=2
UNTIL a = 0 AND b = 0

RETURN unicodeHEX
ENDSUB

SUB GetHEX(int t), string
string hex = HEX$(t)
if hex = "0" THEN hex = "00"
RETURN hex
ENDSUB


So it's not quite right here yet.

I've tried variations for the variable "DisplayText" such as IWSTRING with a size, dbBindParameter(hStmt,17,notes,0,255)
and dbBindParameter(hStmt,17,notes,255)  or 510 - but still can't store Unicode from the edit control.

But interestingly.....

If I change dbBindParameter(hStmt,17,notes,255)
to dbBindParameter(hStmt,17,notesx,255) it's being stored in the next field "notes2".


Andy.
Day after day, day after day, we struck nor breath nor motion, as idle as a painted ship upon a painted ocean.

fasecero

I see, the problem may be due to many causes, I'll not be able to find out until I write a small gui version of my sample code and paste non english characters. I'll try to do it soon. Meanwhile, maybe someone else can come up with another solution :)

fasecero

Yesterday, I made a few small additional fixes to WstringToHexString/GetHEX. I just made a GUI sample - working fine from my side.


$INCLUDE "windowssdk.inc"
$INCLUDE "shlwapi.inc"

'------------------------------------------
' VARIABLES
'------------------------------------------

IWSTRING buffer[255] ' 510 seems to be max allowed size for BINARY
INT databaseCreated = 0
INT j

'------------------------------------------
' INTERFACE
'------------------------------------------

CONST BUTTON_1 = 1
CONST BUTTON_2 = 2
CONST EDIT_3 = 3
CONST BUTTON_4 = 4
CONST STATIC_5 = 5
CONST STATIC_6 = 6
CONST EDIT_7 = 7
CONST STATIC_8 = 8
CONST EDIT_9 = 9
CONST STATIC_10 = 10
CONST EDIT_11 = 11
WINDOW w1
OPENWINDOW w1,0,0,553,550,@SIZE,NULL,"Simple Window",&w1_handler
ENABLETABS w1, 1
SETWINDOWCOLOR w1, GetSysColor(COLOR_3DFACE)
CONTROL w1,@SYSBUTTON,"Create Database",43,15,193,45,0x50000000,BUTTON_1
CONTROL w1,@BUTTON,"Delete Database",281,16,218,42,0x50000000,BUTTON_2
CONTROL w1,@EDIT,"",135,104,229,25,0x50800000|@TABSTOP,EDIT_3
CONTROL w1,@SYSBUTTON,"Add Record and save it to the Databse",378,103,123,142,0x50002000,BUTTON_4
CONTROL w1,@STATIC,"First name",45,109,70,20,0x5000010B,STATIC_5
CONTROL w1,@STATIC,"Last name",45,148,70,20,0x5000010B,STATIC_6
CONTROL w1,@EDIT,"",137,145,228,22,0x50800000|@TABSTOP,EDIT_7
CONTROL w1,@STATIC,"Info",48,193,70,20,0x5000010B,STATIC_8
'CONTROL w1,@EDIT,"",138,190,228,57,0x50800004,EDIT_9
UnicodeEditCreate(w1, EDIT_9, 138, 190, 228, 57, 0)
CONTROL w1,@STATIC,"After a new record is added, the database is (re)loaded below",53,295,448,27,0x5000010B,STATIC_10
'CONTROL w1,@LISTBOX,"ListBox1",53,334,448,140,0x50800340,EDIT_11
UnicodeEditCreate(w1, EDIT_11, 53, 334, 448, 140, 1)

OnInit()

' main loop
WAITUNTIL w1 = 0
END

' window procedure
SUB w1_handler(), INT
SELECT @MESSAGE
CASE @IDCREATE
CENTERWINDOW w1

CASE @IDCLOSEWINDOW
OnClose()
CLOSEWINDOW w1

CASE @IDCONTROL
SELECT @CONTROLID
CASE BUTTON_1
IF @NOTIFYCODE = 0
OnCreateDatabase()
ENDIF
CASE BUTTON_2
IF @NOTIFYCODE = 0
OnDeleteDatabase()
ENDIF
CASE BUTTON_4
IF @NOTIFYCODE = 0
OnNewRecord()
ENDIF
ENDSELECT
ENDSELECT

RETURN 0
ENDSUB

'------------------------------------------
' EVENTS
'------------------------------------------

SUB OnInit()
FOR j = 1 TO 11
SETFONT w1,"Segoe UI",11,400,0,j
NEXT j

databaseCreated = PathFileExists(GETSTARTPATH() + "mydatabase.mdb")
UpdateUI()

IF databaseCreated THEN LoadDatabase()
ENDSUB

SUB OnClose()
' nothing?
ENDSUB

SUB OnCreateDatabase()
CreateNewDatabase()
ENDSUB

SUB OnDeleteDatabase()
DeleteDatabase()
ENDSUB

SUB OnNewRecord()
CreateNewRecord()
ENDSUB

'------------------------------------------
' DATABASE
'------------------------------------------

SUB CreateNewDatabase()
'create a database
INT dbH = dbCreateMDB(GETSTARTPATH() + "mydatabase.mdb")

IF dbH THEN
'make a connection to the newly created database
pointer pdb = dbConnect("Microsoft Access Driver (*.mdb)", GETSTARTPATH() + "mydatabase.mdb", ";")

IF pdb THEN
'create a table and fields
INT hstmt = dbExecSQL(pdb, "CREATE TABLE account (lname VARCHAR(255),fname VARCHAR(255),other VARBINARY(510))" )

IF hstmt THEN
databaseCreated = 1
UpdateUI()

dbFreeSQL(hstmt)
ENDIF
ENDIF

dbDisconnect(pdb)
ENDIF
ENDSUB

SUB DeleteDatabase()
IF databaseCreated THEN
DELETEFILE GETSTARTPATH() + "mydatabase.mdb"

databaseCreated = 0
ResetUI(1)
UpdateUI()
ENDIF
ENDSUB

SUB CreateNewRecord()
' check if an edit is empty
INT empty_edit = 0

string firstName = GETCONTROLTEXT(w1, EDIT_3)
string lastName = GETCONTROLTEXT(w1, EDIT_7)
UnicodeEditGetText(w1, EDIT_9, buffer, 255)

IF LEN(firstName) = 0 THEN empty_edit = 1
IF LEN(lastName) = 0 THEN empty_edit = 1
IF LEN(buffer) = 0 THEN empty_edit = 1

IF empty_edit THEN
MESSAGEBOX(w1, "Empty Edit Box", "error", @MB_ICONSTOP)
RETURN
ENDIF

'make a connection to the newly created database
pointer pdb = dbConnect("Microsoft Access Driver (*.mdb)", GETSTARTPATH() + "mydatabase.mdb", ";")

IF pdb THEN
DatabaseAddRecord(pdb, lastname, firstname, buffer)
ResetUI(0)
dbDisconnect(pdb)

LoadDatabase()
ENDIF
ENDSUB

SUB DatabaseAddRecord(pointer pdb, string lastname, string firstname, wstring unicodeData)
' convert wstring unicodeData -> string unicodeHEX
istring unicodeHEX[510] = WstringToHexString(unicodeData)

INT hstmt = dbPrepareSQL(pdb,"INSERT INTO account (lname,fname,other) VALUES(?,?," + unicodeHEX + ")")

IF hstmt
string first, last
dbBindParameter(hstmt, 1, last, 255)
dbBindParameter(hstmt, 2, first, 255)
first = firstname
last = lastname
dbExecute(hstmt)

dbFreeSQL(hstmt)
ENDIF
ENDSUB

SUB WstringToHexString(wstring unicodeData), string
istring unicodeHEX[510] = "0x"
pointer p = &unicodeData + 0
char a = 0
char b = 0

DO
a = *<char>p
int t = ASC(chr$(a))
unicodeHEX += GetHEX(t)
b = *<char>(p+1)
int t2 = ASC(chr$(b))
unicodeHEX += GetHEX(t2)
p+=2
UNTIL a = 0 AND b = 0

RETURN unicodeHEX
ENDSUB

SUB GetHEX(int t), string
istring hex[3] = HEX$(t)
if LEN(hex) = 1 THEN hex = "0" + hex
RETURN hex
ENDSUB

SUB LoadDatabase()
IWSTRING text[10000] = L""

'make a connection to the newly created database
pointer pdb = dbConnect("Microsoft Access Driver (*.mdb)", GETSTARTPATH() + "mydatabase.mdb", "")

IF pdb THEN
string first, last
INT branch_null

INT hStmt = dbExecSQL(pdb,"SELECT * FROM account")

IF hStmt THEN
dbBindVariable(hstmt, 1, last)
dbBindVariable(hstmt, 2, first)
dbBindVariable(hstmt, 3, buffer, branch_null, 510)

WHILE dbGet(hStmt)
text += S2W(first) + L" - " + S2W(last) + L" - " + buffer + L"\n"
ENDWHILE

UnicodeEditSetText(w1, EDIT_11, text)

dbFreeSQL(hStmt)
ENDIF

dbDisconnect(pdb)
ENDIF
ENDSUB


'------------------------------------------
' UI PROCEDURES
'------------------------------------------

SUB UpdateUI()
ENABLECONTROL w1, BUTTON_1, InvertFlag(databaseCreated)
ENABLECONTROL w1, BUTTON_2, databaseCreated
ENABLECONTROL w1, EDIT_3, databaseCreated
ENABLECONTROL w1, EDIT_7, databaseCreated
ENABLECONTROL w1, EDIT_9, databaseCreated
ENABLECONTROL w1, BUTTON_4, databaseCreated
ENABLECONTROL w1, EDIT_11, FALSE
ENDSUB

SUB ResetUI(INT allofthem)
SETCONTROLTEXT w1, EDIT_3, ""
SETCONTROLTEXT w1, EDIT_7, ""
UnicodeEditSetText(w1, EDIT_9, L"")
IF allofthem THEN UnicodeEditSetText(w1, EDIT_11, L"")
ENDSUB

SUB InvertFlag(INT flag), INT
IF flag THEN RETURN 0
RETURN 1
ENDSUB

'------------------------------------------
' UNICODE EDIT CONTROL
'------------------------------------------

SUB UnicodeEditCreate(window w, INT ctrlID, INT x, INT y, INT width, INT height, INT multiline)
INT dwStyles = WS_CHILD | WS_VISIBLE | ES_LEFT | WS_TABSTOP
IF multiline THEN dwStyles = dwStyles | WS_VSCROLL | ES_MULTILINE
INT hwndEDIT = CreateWindowExW(WS_EX_CLIENTEDGE, L"EDIT",NULL, dwStyles, x, y, width, height, w.hwnd, ctrlID, GetModuleHandle(0), NULL)
ENDSUB

SUB UnicodeEditGetText(window w, INT ctrlID, pointer buffer, INT maxCount)
INT hwndEDIT = GetDlgItem(w.hwnd, ctrlID)
GetWindowTextW(hwndEDIT, buffer, maxCount)
ENDSUB

SUB UnicodeEditSetText(window w, INT ctrlID, pointer buffer)
INT hwndEDIT = GetDlgItem(w.hwnd, ctrlID)
SetWindowTextW(hwndEDIT, buffer)
ENDSUB

Andy

December 13, 2018, 03:54:12 AM #19 Last Edit: December 13, 2018, 06:18:10 AM by Andy
Fasecero,

That is actually superb work!

Yes it works here too, but I've amended a test version of my main program (with a new test DB) and it works on that too.

Just one question (and there's always one more - sorry), when I change you code to multi line like this:

UnicodeEditCreate(w1, EDIT_9, 10, 190, 1000, 57, 1)

and then type away onto a second line then save, it's not correct when it saves.

I will have a look now.

Thanks,
Andy.
:)

Day after day, day after day, we struck nor breath nor motion, as idle as a painted ship upon a painted ocean.

Andy

December 13, 2018, 04:42:47 AM #20 Last Edit: December 13, 2018, 06:19:06 AM by Andy
Well the one thing I can work out is that:

If 510 is the maximum length you can store then the string length you can have is this -

510 - 6 characters "0x and 0000" - that is the start / end of the string.

So that gives you 504 characters available.

504 / 4 (per character) = 126.

So the maximum characters you can store is 126 - which is a big improvement from the 40 or so I could do.

So finally, it's not a multi line issue, it's the string length that matters - and a check must be put in place for this I think.

Andy.
Day after day, day after day, we struck nor breath nor motion, as idle as a painted ship upon a painted ocean.

Andy

December 13, 2018, 06:14:49 AM #21 Last Edit: December 13, 2018, 06:19:35 AM by Andy
And one final interesting note:

This is the update code for my main program

iStmt="UPDATE customers SET lName=?,fName=?,oName=?,sex=?,street=?,city=?,region=?,"
iStmt+="tk=?,phone=?,mobile=?,facebook=?,dob=?,age=?,email=?,cwork=?,disc=?,notes="+notesx+",notes2=?,notes3=?,notes4=?,cphoto=?"
iStmt+=" WHERE recID="+STR$(id)
hStmt=dbPrepareSQL(pDB,iStmt)
dbBindParameter(hStmt,1,lName,200)
dbBindParameter(hStmt,2,fName,200)
dbBindParameter(hStmt,3,oName,200)
dbBindParameter(hStmt,4,sex,5)
dbBindParameter(hStmt,5,street,200)
dbBindParameter(hStmt,6,city,40)
dbBindParameter(hStmt,7,region,40)
dbBindParameter(hStmt,8,tk,10)
dbBindParameter(hStmt,9,phone,40)
dbBindParameter(hStmt,10,mobile,40)
dbBindParameter(hStmt,11,facebook,40)
dbBindParameter(hStmt,12,isoDate,11)
dbBindParameter(hStmt,13,age,5)
dbBindParameter(hStmt,14,email,40)
dbBindParameter(hStmt,15,cwork,40)
dbBindParameter(hStmt,16,disc,8)
dbBindParameter(hStmt,17,notes2,255)
dbBindParameter(hStmt,18,notes3,255)
dbBindParameter(hStmt,19,notes4,255)
dbBindParameter(hStmt,20,cphoto,255)


If you look closely at it, bind parameter for "notes" is left out, and that's the only way I found it to work.

Is this correct?

All I can say is it works here.

Andy.
Day after day, day after day, we struck nor breath nor motion, as idle as a painted ship upon a painted ocean.

fasecero

QuoteIf you look closely at it, bind parameter for "notes" is left out, and that's the only way I found it to work.

Is this correct?

Yes, don't bind the unicode parameter and use the hex "trick" :)

QuoteIf 510 is the maximum length you can store then the string length you can have is this -

510 - 6 characters "0x and 0000" - that is the start / end of the string.

So that gives you 504 characters available.

504 / 4 (per character) = 126.

The example seems to agree with you about this, just tried an unicode with 130 characters and the application has crashed. Even so, inside the database each character takes only 2 bytes long, so it should be at least 504/2 (per character) = 252. Something else must be wrong in my code.

fasecero


wow. I made a modification and now you can store up to any unicode size!  ;D


$INCLUDE "windowssdk.inc"
$INCLUDE "shlwapi.inc"

'------------------------------------------
' VARIABLES
'------------------------------------------

SETPRECISION 0
$DEFINE MAX_UNICHARS 1000  ' MAXIMUN NUMBER OF UNICODE CHARACTER ALLOWED/WANTED

IWSTRING buffer[MAX_UNICHARS]
INT databaseCreated = 0
INT j

'------------------------------------------
' INTERFACE
'------------------------------------------

CONST BUTTON_1 = 1
CONST BUTTON_2 = 2
CONST EDIT_3 = 3
CONST BUTTON_4 = 4
CONST STATIC_5 = 5
CONST STATIC_6 = 6
CONST EDIT_7 = 7
CONST STATIC_8 = 8
CONST EDIT_9 = 9
CONST STATIC_10 = 10
CONST EDIT_11 = 11
WINDOW w1
OPENWINDOW w1,0,0,553,550,@SIZE,NULL,"Simple Window",&w1_handler
ENABLETABS w1, 1
SETWINDOWCOLOR w1, GetSysColor(COLOR_3DFACE)
CONTROL w1,@SYSBUTTON,"Create Database",43,15,193,45,0x50000000,BUTTON_1
CONTROL w1,@BUTTON,"Delete Database",281,16,218,42,0x50000000,BUTTON_2
CONTROL w1,@EDIT,"",135,104,229,25,0x50800000|@TABSTOP,EDIT_3
CONTROL w1,@SYSBUTTON,"Add Record and save it to the Databse",378,103,123,142,0x50002000,BUTTON_4
CONTROL w1,@STATIC,"First name",45,109,70,20,0x5000010B,STATIC_5
CONTROL w1,@STATIC,"Last name",45,148,70,20,0x5000010B,STATIC_6
CONTROL w1,@EDIT,"",137,145,228,22,0x50800000|@TABSTOP,EDIT_7
CONTROL w1,@STATIC,"Info",48,193,70,20,0x5000010B,STATIC_8
'CONTROL w1,@EDIT,"",138,190,228,57,0x50800004,EDIT_9
UnicodeEditCreate(w1, EDIT_9, 138, 190, 228, 57, 1)
CONTROL w1,@STATIC,"After a new record is added, the database is (re)loaded below",53,295,448,27,0x5000010B,STATIC_10
'CONTROL w1,@LISTBOX,"ListBox1",53,334,448,140,0x50800340,EDIT_11
UnicodeEditCreate(w1, EDIT_11, 53, 334, 448, 140, 1)

OnInit()

' main loop
WAITUNTIL w1 = 0
END

' window procedure
SUB w1_handler(), INT
SELECT @MESSAGE
CASE @IDCREATE
CENTERWINDOW w1

CASE @IDCLOSEWINDOW
OnClose()
CLOSEWINDOW w1

CASE @IDCONTROL
SELECT @CONTROLID
CASE BUTTON_1
IF @NOTIFYCODE = 0
OnCreateDatabase()
ENDIF
CASE BUTTON_2
IF @NOTIFYCODE = 0
OnDeleteDatabase()
ENDIF
CASE BUTTON_4
IF @NOTIFYCODE = 0
OnNewRecord()
ENDIF
ENDSELECT
ENDSELECT

RETURN 0
ENDSUB

'------------------------------------------
' EVENTS
'------------------------------------------

SUB OnInit()
FOR j = 1 TO 11
SETFONT w1,"Segoe UI",11,400,0,j
NEXT j

databaseCreated = PathFileExists(GETSTARTPATH() + "mydatabase.mdb")
UpdateUI()

IF databaseCreated THEN LoadDatabase()
ENDSUB

SUB OnClose()
' nothing?
ENDSUB

SUB OnCreateDatabase()
CreateNewDatabase()
ENDSUB

SUB OnDeleteDatabase()
DeleteDatabase()
ENDSUB

SUB OnNewRecord()
CreateNewRecord()
ENDSUB

'------------------------------------------
' DATABASE
'------------------------------------------

SUB CreateNewDatabase()
'create a database
INT dbH = dbCreateMDB(GETSTARTPATH() + "mydatabase.mdb")

IF dbH THEN
'make a connection to the newly created database
pointer pdb = dbConnect("Microsoft Access Driver (*.mdb)", GETSTARTPATH() + "mydatabase.mdb", ";")

IF pdb THEN
'create a table and fields
INT hstmt = dbExecSQL(pdb, "CREATE TABLE account (lname VARCHAR(255),fname VARCHAR(255),other VARBINARY(510))" )

IF hstmt THEN
databaseCreated = 1
UpdateUI()

dbFreeSQL(hstmt)
ENDIF
ENDIF

dbDisconnect(pdb)
ENDIF
ENDSUB

SUB DeleteDatabase()
IF databaseCreated THEN
DELETEFILE GETSTARTPATH() + "mydatabase.mdb"

databaseCreated = 0
ResetUI(1)
UpdateUI()
ENDIF
ENDSUB

SUB CreateNewRecord()
' check if an edit is empty
INT empty_edit = 0

string firstName = GETCONTROLTEXT(w1, EDIT_3)
string lastName = GETCONTROLTEXT(w1, EDIT_7)
UnicodeEditGetText(w1, EDIT_9, buffer, MAX_UNICHARS)

IF LEN(firstName) = 0 THEN empty_edit = 1
IF LEN(lastName) = 0 THEN empty_edit = 1
IF LEN(buffer) = 0 THEN empty_edit = 1

IF empty_edit THEN
MESSAGEBOX(w1, "Empty Edit Box", "error", @MB_ICONSTOP)
RETURN
ENDIF

'make a connection to the newly created database
pointer pdb = dbConnect("Microsoft Access Driver (*.mdb)", GETSTARTPATH() + "mydatabase.mdb", ";")

IF pdb THEN
DatabaseAddRecord(pdb, lastname, firstname, buffer)
ResetUI(0)
dbDisconnect(pdb)

LoadDatabase()
ENDIF
ENDSUB

SUB DatabaseAddRecord(pointer pdb, string lastname, string firstname, wstring unicodeData)
' convert wstring unicodeData -> string unicodeHEX
istring unicodeHEX[4 * MAX_UNICHARS + 6] = WstringToHexString(unicodeData)

INT hstmt = dbPrepareSQL(pdb,"INSERT INTO account (lname,fname,other) VALUES(?,?," + unicodeHEX + ")")

IF hstmt
string first, last
dbBindParameter(hstmt, 1, last, 255)
dbBindParameter(hstmt, 2, first, 255)
first = firstname
last = lastname
dbExecute(hstmt)

dbFreeSQL(hstmt)
ENDIF
ENDSUB

SUB WstringToHexString(wstring unicodeData), string
istring unicodeHEX[4 * MAX_UNICHARS + 6] = "0x"
pointer p = &unicodeData + 0
char a = 0
char b = 0

DO
a = *<char>p
int t = ASC(chr$(a))
unicodeHEX += GetHEX(t)
b = *<char>(p+1)
int t2 = ASC(chr$(b))
unicodeHEX += GetHEX(t2)
p+=2
UNTIL a = 0 AND b = 0

RETURN unicodeHEX
ENDSUB

SUB GetHEX(int t), string
istring hex[10] = HEX$(t)
if LEN(hex) = 1 THEN hex = "0" + hex
RETURN hex
ENDSUB

SUB LoadDatabase()
IWSTRING text[10000] = L""

'make a connection to the newly created database
pointer pdb = dbConnect("Microsoft Access Driver (*.mdb)", GETSTARTPATH() + "mydatabase.mdb", "")

IF pdb THEN
string first, last
INT branch_null

INT hStmt = dbExecSQL(pdb,"SELECT * FROM account")

IF hStmt THEN
dbBindVariable(hstmt, 1, last)
dbBindVariable(hstmt, 2, first)
dbBindVariable(hstmt, 3, buffer, branch_null, 2* MAX_UNICHARS)

WHILE dbGet(hStmt)
text += S2W(first) + L" - " + S2W(last) + L" - " + buffer + L" (" + WLTRIM$(wstr$(len(buffer))) + L" characters long)" + L"\n"
ENDWHILE

UnicodeEditSetText(w1, EDIT_11, text)

dbFreeSQL(hStmt)
ENDIF

dbDisconnect(pdb)
ENDIF
ENDSUB


'------------------------------------------
' UI PROCEDURES
'------------------------------------------

SUB UpdateUI()
ENABLECONTROL w1, BUTTON_1, InvertFlag(databaseCreated)
ENABLECONTROL w1, BUTTON_2, databaseCreated
ENABLECONTROL w1, EDIT_3, databaseCreated
ENABLECONTROL w1, EDIT_7, databaseCreated
ENABLECONTROL w1, EDIT_9, databaseCreated
ENABLECONTROL w1, BUTTON_4, databaseCreated
ENABLECONTROL w1, EDIT_11, TRUE
ENDSUB

SUB ResetUI(INT allofthem)
SETCONTROLTEXT w1, EDIT_3, ""
SETCONTROLTEXT w1, EDIT_7, ""
UnicodeEditSetText(w1, EDIT_9, L"")
IF allofthem THEN UnicodeEditSetText(w1, EDIT_11, L"")
ENDSUB

SUB InvertFlag(INT flag), INT
IF flag THEN RETURN 0
RETURN 1
ENDSUB

'------------------------------------------
' UNICODE EDIT CONTROL
'------------------------------------------

SUB UnicodeEditCreate(window w, INT ctrlID, INT x, INT y, INT width, INT height, INT multiline)
INT dwStyles = WS_CHILD | WS_VISIBLE | ES_LEFT | WS_TABSTOP
IF multiline THEN dwStyles = dwStyles | WS_VSCROLL | ES_MULTILINE | ES_WANTRETURN
INT hwndEDIT = CreateWindowExW(WS_EX_CLIENTEDGE, L"EDIT",NULL, dwStyles, x, y, width, height, w.hwnd, ctrlID, GetModuleHandle(0), NULL)
ENDSUB

SUB UnicodeEditGetText(window w, INT ctrlID, pointer buffer, INT maxCount)
INT hwndEDIT = GetDlgItem(w.hwnd, ctrlID)
GetWindowTextW(hwndEDIT, buffer, maxCount)
ENDSUB

SUB UnicodeEditSetText(window w, INT ctrlID, pointer buffer)
INT hwndEDIT = GetDlgItem(w.hwnd, ctrlID)
SetWindowTextW(hwndEDIT, buffer)
ENDSUB

Andy

Wow is the word!

Fasecero, that's so brilliant - I've now been able to amend my test program to accept Unicode notes and it work very well.

I've got a few other fields I'm going to amend - but this is just superb! (is that enough thanks lol?).

Hopefully one day, I might actually be able to help you in return.

Andy.
:)  :)

Day after day, day after day, we struck nor breath nor motion, as idle as a painted ship upon a painted ocean.