Thanks Dana,
I think you're talking about doing what I did, but I'm not 100% sure.
Below is my solution. Could you let me know if yours is the same or
something more elegant?
If it's the same, maybe this code will be helpful for other people. How'd I
do? (I'm not a pro, so go easy):
Public Sub InsertSongVers(ByVal tableWanted As String)
'SQL Server
Dim myTable As String = tableWanted
dim sConnString As String = "..." 'insert your
connection to SQL string here
dim myconnection As New SqlConnection(sConnString)
myconnection.Open()
Dim SCMSdataadapter As New SqlDataAdapter("Select * from " &
myTable, myconnection)
Dim dtSCMS As New DataTable(myTable)
SCMSdataadapter.Fill(dtSCMS)
myconnection.Close()
'create an array with the column data types
Dim mySQLFields As String = "("
Dim columnNames = New List(Of String)
Dim columnTypes = New List(Of String)
For Each column As DataColumn In dtSCMS.Columns
Dim newItem As String = column.ColumnName
columnNames.Add(newItem)
Dim newTypeItem As String
newTypeItem = column.DataType.ToString
columnTypes.Add(newTypeItem)
If mySQLFields = "(" Then
mySQLFields = mySQLFields & "[" & newItem & "]"
Else
mySQLFields = mySQLFields & ",[" & newItem & "]"
End If
Next
mySQLFields = mySQLFields & ")"
Debug.Writeline("Here's the list of data types: " & mySQLFields)
'create a string with the data
Dim myRowNum As Integer = 0
For Each row As DataRow In dtSCMS.Rows
Dim mySQLRowData As String = "("
Dim columnIndex As Integer = -1 ' because first index is 0
For Each cell In row.ItemArray
columnIndex = columnIndex + 1
Dim myAddSt As String = cell.ToString
Dim myType As String = ""
Select Case columnTypes.Item(columnIndex)
Case "System.String" 'nvarchar(50)
myAddSt = "'" & Replace(myAddSt, "'", "''") & "'"
Case "System.Int32" 'bigint
If cell.GetType.ToString = "System.DBNull" Then
myAddSt = "'null'"
Case "System.DBNull" 'null -don't think this can
happen now
Case "System.DateTime" 'datetime
myAddSt = "'" & myAddSt & "'"
Case "System.Boolean" 'bit
Select Case myAddSt
Case "False"
myAddSt = "0"
Case "True"
myAddSt = "1"
Case IsNothing(myAddSt) 'don't know if this
one's possible, but what the hey
myAddSt = "0"
Case ""
myAddSt = "0"
End Select
Case "System.Byte[]"
Dim rowNumber As Int64 = BitConverter.ToInt64(cell,
0)
myAddSt = rowNumber.ToString() '"'" & myAddSt & "'"
Case Else
MsgBox("Didn't expect to see " &
cell.GetType.ToString & " for type. Check it out.")
End Select
If mySQLRowData = "(" Then
mySQLRowData = mySQLRowData & myAddSt
Else
mySQLRowData = mySQLRowData & "," & myAddSt
End If
Next
mySQLRowData = mySQLRowData & ");"
Debug.WriteLine("Here's the data: " & mySQLRowData)
Dim mySQLUp As String = ("INSERT INTO [" & myTable & "] " &
mySQLFields & " VALUES " & mySQLRowData)
Debug.WriteLine("Here's the full SQLite syntax: " & mySQLUp)
Try
'SQLite
Dim connectionString As String = "Data Source=...path to
your Database"
Dim dbConnection As New SQLiteConnection(connectionString)
dbConnection.Open()
Dim command = New SQLiteCommand(mySQLUp, dbConnection)
command.ExecuteNonQuery()
dbConnection.Close()
Catch ex As Exception
If ex.Message = "constraint failed" & vbCrLf & "UNIQUE
constraint failed: SongVersionsTEST.SongVerID" Then
Debug.WriteLine("That record is already in the table.")
'I think this line came from the "INSERT" version of this code I wrote and
probably don't need it here, sorry.
End If
End Try
myRowNum = myRowNum + 1
Debug.WriteLine(myRowNum)
Next 'next row
MsgBox("All done! " & myRowNum & " records")
-----Original Message-----
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of dmp
Sent: Wednesday, July 20, 2016 10:00 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] SQL to SQLite
> Message: 2
> Date: Tue, 19 Jul 2016 15:01:55 -0700
> From: <tm...@spotlightmusic.com>
> To: <sqlite-users@mailinglists.sqlite.org>
> Subject: [sqlite] SQL to SQLite
> Message-ID: <004d01d1e209$2a356360$7ea02a20$@spotlightmusic.com>
> Content-Type: text/plain; charset="us-ascii"
>
> What's the best way to Insert or Update records from a connected SQL
> database to the connected SQLite database?
>
> I don't mean just once, but to do every so often.
> Thanks.
> Thanks for your response.
> Programming language is definitely the way I want to go, and in fact I
> programmed a workaround, but I assume I took the long way around and
> that there's a more standard way to do it.
Hello,
If the source database is different, or SQLite, than the destination
database, SQLite, can be done and I have been working on a bridge in Java
that will perform the transfer. It is functional, but needs further work and
testing.
Essentially:
Create an ArrayBlockingQueue, start a LoadThread and a InsertPrepareThread.
Define the ArrayBlockingQueue as objects of a relation row element. Have the
load thread fill the blocking queue then the insert prepare thread consume
the table row elements from the queue.
A single SQL query can be used to define the SQLite table then fill it with
the source database data. A type definitions conversion needs to take place
to correctly transfer db --> db data types.
I have defined these type info conversions for various database and it is
available as part of my MyJSQLView project. At this time the db --> db code
is a plugin for MyJSQLView and is not been released to the public.
https://github.com/danap/myjsqlview/blob/master/src/com/dandymadeproductions
/myjsqlview/datasource/TypesInfoCache.java
Dana Proctor
MyJSQLView Project Manager
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
